package com.neutech.studentgrade.dao.impl;

import com.neutech.studentgrade.dao.GradeDAO;
import com.neutech.studentgrade.entity.Grade;
import com.neutech.studentgrade.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class GradeDAOImpl implements GradeDAO {
    @Override
    public  List<Grade> findGradeBySno(String sno) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Grade grade = null;
//        String sql = "select id,user_name,user_password,user_phone,user_addr,user_createTime,user_updateTime,user_status from" +
//                "t_user where id = ?";
        String sql = "select sc.sno,student.sname,course.cno,course.cname,sc.grade from " +
                "sc,course,student where sc.cno = course.cno and sc.sno=student.sno AND sc.sno=? GROUP BY sc.cno,sc.sno" ;
        List<Grade> grades = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,sno);
            resultSet = JdbcUtil.getResultSet(preparedStatement);
            while (resultSet.next()){
                grade = new Grade();
                grade.setSno(resultSet.getNString("sno"));
                grade.setSname(resultSet.getNString("sname"));
                grade.setCno(resultSet.getNString("cno"));
                grade.setCname(resultSet.getNString("cname"));
                grade.setGrade(resultSet.getInt("grade"));
                grades.add(grade);
//                System.out.println(1);
            }
        }catch (Exception e){
            e.printStackTrace();
            //出现异常，事务不提交，回滚
            JdbcUtil.rollback(connection);
        }finally {
            JdbcUtil.close(resultSet,preparedStatement,connection);
        }
        return grades;
    }

    public static void main(String[] args) {
        GradeDAO dao = new GradeDAOImpl();
        System.out.println(dao.findGradeBySno("S001"));
    }

    @Override
    public void updateGrade(Grade grade) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "update sc set sc.grade = ? where sc.sno = ? and sc.cno = ?";
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection, sql);
            preparedStatement.setInt(1, grade.getGrade());
            preparedStatement.setString(2,grade.getSno());
            preparedStatement.setString(3,grade.getCno());
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Grade findGradeBySno1(String sno,String cno) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Grade grade = null;
//        String sql = "select id,user_name,user_password,user_phone,user_addr,user_createTime,user_updateTime,user_status from" +
//                "t_user where id = ?";
        String sql = "select sc.sno,student.sname,course.cno,course.cname,sc.grade from " +
                "sc,course,student where sc.cno = course.cno and sc.sno=student.sno AND sc.sno=? and sc.cno=? GROUP BY sc.cno,sc.sno" ;
        List<Grade> grades = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,sno);
            preparedStatement.setString(2,cno);
            resultSet = JdbcUtil.getResultSet(preparedStatement);
            while (resultSet.next()){
                grade = new Grade();
                grade.setSno(resultSet.getNString("sno"));
                grade.setSname(resultSet.getNString("sname"));
                grade.setCno(resultSet.getNString("cno"));
                grade.setCname(resultSet.getNString("cname"));
                grade.setGrade(resultSet.getInt("grade"));
                grades.add(grade);
//                System.out.println(1);
            }
        }catch (Exception e){
            e.printStackTrace();
            //出现异常，事务不提交，回滚
            JdbcUtil.rollback(connection);
        }finally {
            JdbcUtil.close(resultSet,preparedStatement,connection);
        }
        return grade;
    }
}
